from flask import Flask, render_template, url_for, request, redirect, session, jsonify
import pymysql
from datetime import timedelta
import jinja2
from dbutils.pooled_db import PooledDB
from config.config import DB_CONFIG, PROJECT_CONFIG
from static.utils.SelectRightSchool import SelectRightSchool, SelectRightAreaSchool
from random import randint
import time

app = Flask(__name__)

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False  # 关闭对模型修改的监控
app.config['SECRET_KEY'] = PROJECT_CONFIG['SECRET_KEY']  # 等同于 app.secret_key = 'dev'
app.config['SESSION_COMPRESS'] = True
app.config['UPLOAD_FOLDER'] = PROJECT_CONFIG['UPLOAD_FOLDER']
AREA_LIST = PROJECT_CONFIG['AREA_LIST']
DONT_SCHOOL_LIST = PROJECT_CONFIG['DONT_SCHOOL_LIST']
MUST_SCHOOL_CODE = PROJECT_CONFIG['MUST_SCHOOL_CODE']
app.permanent_session_lifetime = timedelta(minutes=10)
env = jinja2.Environment()
env.policies['json.dumps_kwargs']['sort_keys'] = False
env.policies['json.dumps_kwargs']['ensure_ascii'] = False
app.config['PERMANENT_SESSION_LIFETIME'] = 7200
pool = PooledDB(pymysql, blocking=True, host=DB_CONFIG["host"], user=DB_CONFIG["user"],
                passwd=DB_CONFIG["password"], database=DB_CONFIG["database"], port=DB_CONFIG["port"],
                charset=DB_CONFIG["charset"])


@app.errorhandler(404)  # 传入要处理的错误代码
def page_not_found(e):  # 接受异常对象作为参数
    return render_template('404.html'), 404  # 返回模板和状态码


@app.route('/')
def Index():
    return render_template('index.html')


# 表单提交后保存学生信息到数据库
@app.route('/saveStudentInfo', methods=['POST'])
def saveStudentInfo():
    if request.method == 'POST':
        studentName = request.form['studentName']
        examScore = request.form['examScore']
        schoolName = request.form['schoolName']
        phoneNumber = request.form['phoneNumber']
        submitTeacher = request.form['registerTeacher']
        studentID = str(examScore) + str(randint(100000, 999999)) + str(time.time())
        conn = pool.connection()
        cursor = conn.cursor()
        # 保存学生信息到数据库
        sql = f"insert into StudentInfo(StudentID,StudentName,StudentScore,SourceSchool,StudentPhone,RegisterTeacher) values('{studentID}','{studentName}',{examScore},'{schoolName}','{phoneNumber}','{submitTeacher}')"
        cursor.execute(sql)
        conn.commit()
        cursor.close()
        conn.close()

        studentInfo = {
            "studentID": studentID,
            "StudentName": studentName,
            "StudentScore": examScore,
            "SchoolName": schoolName
        }
        # 将数据传给志愿处理函数
        session['studentInfo'] = studentInfo
        return redirect(url_for('showMatchSchool'))


@app.route('/searchStudentInfo', methods=['POST'])
def searchStudentInfo():
    recv = request.json
    # print(recv)
    studentID = recv['studentID']
    # print(studentID)
    if studentID:
        conn = pool.connection()
        cursor = conn.cursor()
        # 查询学生信息
        sql = f"select StudentName,StudentScore,SourceSchool from StudentInfo where StudentID = '{studentID}'"
        cursor.execute(sql)
        result = cursor.fetchone()
        responseJson = {}
        if result is None:
            responseJson['status'] = 0
            return jsonify()
        studentName = result[0]
        examScore = result[1]
        schoolName = result[2]

        cursor.close()
        conn.close()

        studentInfo = {
            "studentID": studentID,
            "StudentName": studentName,
            "StudentScore": examScore,
            "SchoolName": schoolName
        }
        # 将数据传给志愿处理函数
        session['studentInfo'] = studentInfo
        responseJson['status'] = 1
        return jsonify(responseJson)


# 确认已经填报志愿
@app.route('/confirm', methods=['POST'])
def confirm():
    recv = request.json
    confirmed = recv['confirm']
    studentInfo = session.get('studentInfo')
    if studentInfo is None:
        return redirect(url_for('Index'))
    studentID = studentInfo['studentID']
    print(studentID)
    conn = pool.connection()
    cursor = conn.cursor()
    responseJson = {}
    sql = f"select StudentConfirm from StudentInfo where StudentID = '{studentID}'"
    cursor.execute(sql)
    result = cursor.fetchone()
    if result is None:
        return redirect(url_for('Index'))
    if result[0] == 0 and confirmed == 1:  # 数据库中未确认,且前端需确认，则确认
        sql = f"update StudentInfo set StudentConfirm = '{confirmed}' where StudentID = '{studentID}'"
        responseJson['status'] = 1
        cursor.execute(sql)
        conn.commit()
        cursor.close()
        conn.close()
    elif result[0] == 0 and confirmed == 0:  # 数据库中未确认，且不需确认
        responseJson['status'] = 0
    else:  # 数据库中已确认，则无需确认
        responseJson['status'] = 1

    return jsonify(responseJson)


@app.route("/showSchool", methods=['GET', "POST"])
def showSchool():
    conn = pool.connection()
    cursor = conn.cursor()
    # 开始计算学校
    sql = (
        f"select SchoolInfo.SchoolCode,SchoolInfo.SchoolName,PreAcceptInfo.PreAcceptScoreBySchool,"
        f"PreAcceptInfo.PreAcceptScoreByAverage,SchoolInfo.SchoolType,SchoolInfo.SchoolLevel,"
        f"SchoolInfo.SchoolArea,SchoolInfo.SchoolAddress,SchoolInfo.SchoolLat,SchoolInfo.SchoolLng "
        f"from PreAcceptInfo "
        f"INNER JOIN SchoolInfo ON PreAcceptInfo.SchoolCode = SchoolInfo.SchoolCode ORDER BY PreAcceptInfo.PreAcceptScoreByAverage DESC ")
    cursor.execute(sql)
    results = cursor.fetchall()
    cursor.close()
    conn.close()
    responseJson = {}
    if results is None:
        return render_template("index.html")

    if len(results) > 0:
        responseJson['alldata'] = {}
        responseJson['alldata']['data'] = []
        for row in results:
            row = list(row)  # 将tuple转成list
            # 将百度地图的经纬度转换为高德地图的，后面的数字是手算的
            # row[-2] = row[-2] - 0.006444 #纬度
            # row[-1] = row[-1] - 0.006618 #经度
            if row[4] == 1:
                row[4] = "公办"
            elif row[4] == 2:
                row[4] = "民办"
            else:
                row[4] = "暂无数据"

            if row[5] == 1:
                row[5] = "省示范"
            elif row[5] == 2:
                row[5] = "省标"
            elif row[5] == 3:
                row[5] = "普高"
            else:
                row[5] = "暂无数据"
            responseJson['alldata']['data'].append(row)
        responseJson['Weiyang'] = SelectRightAreaSchool(schoolList=responseJson['alldata'], AreaList=AREA_LIST,
                                                        dontSchoolList=DONT_SCHOOL_LIST)
    return render_template("showSchoolList.html", allSchool=responseJson)


@app.route('/showMatchSchool', methods=['GET', 'POST'])
def showMatchSchool():
    conn = pool.connection()
    cursor = conn.cursor()
    # 开始计算学校
    sql = (
        f"select SchoolInfo.SchoolCode,SchoolInfo.SchoolName,PreAcceptInfo.PreAcceptScoreBySchool,"
        f"PreAcceptInfo.PreAcceptScoreByAverage,SchoolInfo.SchoolType,SchoolInfo.SchoolLevel,"
        f"SchoolInfo.SchoolArea,SchoolInfo.SchoolAddress,SchoolInfo.SchoolLat,SchoolInfo.SchoolLng "
        f"from PreAcceptInfo "
        f"INNER JOIN SchoolInfo ON PreAcceptInfo.SchoolCode = SchoolInfo.SchoolCode ORDER BY PreAcceptInfo.PreAcceptScoreByAverage DESC ")
    cursor.execute(sql)
    results = cursor.fetchall()
    cursor.close()
    conn.close()
    studentInfo = session.get('studentInfo')
    if studentInfo is None:
        return redirect(url_for('Index'))
    # 使用学校提供的预估分
    schoolDictBySchool = {  # 差值=学校预录取分-学生中考分
        'TooFarList': [],  # 差值>20 基本没可能
        'UpList': [],  # 10<=差值<=20，可以冲一下，概率不大，但有可能
        'OptimalList': [],  # 0<=差值<10 可以冲且概率比较大
        'BefitList': [],  # 0>差值>=-10 比较稳妥的选择，大概率被录取
        'DownList': [],  # -20<=差值<=-10 90%以上概率被录取,但仍会有风险
        'TooLowList': [],  # -40<差值<-20,保底
    }
    # 使用计算得到的预估分
    schoolDictByAverage = {  # 差值=学校预录取分-学生中考分
        'TooFarList': [],  # 差值>20 基本没可能
        'UpList': [],  # 10<=差值<=20，可以冲一下，概率不大，但有可能
        'OptimalList': [],  # 0<=差值<10 可以冲且概率比较大
        'BefitList': [],  # 0>差值>=-10 比较稳妥的选择，大概率被录取
        'DownList': [],  # -20<=差值<=-10 90%以上概率被录取,但仍会有风险
        'TooLowList': [],  # -40<差值<-20,保底

    }
    schoolListAll = []
    if results is None:
        return "<script>alert('无法找到满足您分数的学校')</script>"
    for row in results:
        row = list(row)  # 将tuple转成list
        # 将百度地图的经纬度转换为高德地图的，后面的数字是手算的
        # row[-2] = row[-2] - 0.006444 #纬度
        # row[-1] = row[-1] - 0.006618 #经度
        if row[4] == 1:
            row[4] = "公办"
        elif row[4] == 2:
            row[4] = "民办"
        else:
            row[4] = "暂无数据"

        if row[5] == 1:
            row[5] = "省示范"
        elif row[5] == 2:
            row[5] = "省标"
        elif row[5] == 3:
            row[5] = "普高"
        else:
            row[5] = "暂无数据"
        schoolListAll.append(row)  # 所有学校的列表
        if row[2]:  # 使用学校提供的预录取分数
            DiffScore = row[2] - float(studentInfo['StudentScore'])
            if DiffScore >= 20:
                schoolDictBySchool["TooFarList"].append(row)
            elif 10 <= DiffScore < 20:
                schoolDictBySchool["UpList"].append(row)
            elif 0 <= DiffScore < 10:
                schoolDictBySchool["OptimalList"].append(row)
            elif -10 <= DiffScore < 0:
                schoolDictBySchool["BefitList"].append(row)
            elif -20 <= DiffScore < -10:
                schoolDictBySchool["DownList"].append(row)
            elif -40 < DiffScore < -20:
                schoolDictBySchool["TooLowList"].append(row)
        if row[3]:  # 使用线差平均值计算出来的预估分数线
            DiffScore = row[3] - float(studentInfo['StudentScore'])
            if DiffScore >= 20:
                schoolDictByAverage["TooFarList"].append(row)
            elif 10 <= DiffScore < 20:
                schoolDictByAverage["UpList"].append(row)
            elif 0 <= DiffScore < 10:
                schoolDictByAverage["OptimalList"].append(row)
            elif -10 <= DiffScore < 0:
                schoolDictByAverage["BefitList"].append(row)
            elif -20 <= DiffScore < -10:
                schoolDictByAverage["DownList"].append(row)
            elif -60 < DiffScore < -20:
                schoolDictByAverage["TooLowList"].append(row)

        # 用于最终展示的学校
        # 用学校提供的预估线筛选的学校,不进行区域限制
        showSchoolList_BySchoolPre = SelectRightSchool(schoolList=schoolDictBySchool, mustSchoolCode=MUST_SCHOOL_CODE)
        # 使用学校提供的预估线，并加上区域限制
        rightAreaSchool_BySchool = SelectRightAreaSchool(schoolList=schoolDictBySchool, AreaList=AREA_LIST,
                                                         dontSchoolList=DONT_SCHOOL_LIST)
        # 从限制后的区域里推荐志愿
        showSchoolList_BySchoolPre_withArea = SelectRightSchool(schoolList=rightAreaSchool_BySchool)

        # 用线差平均值算法计算出来的预估线筛选的学校，不进行区域限制
        showSchoolList_ByAveragePre = SelectRightSchool(schoolList=schoolDictByAverage, mustSchoolCode=MUST_SCHOOL_CODE)
        # 使用线差平均值法计算出来的预估线，并加上区域限制
        rightAreaSchool_ByAverage = SelectRightAreaSchool(schoolList=schoolDictByAverage, AreaList=AREA_LIST,
                                                          dontSchoolList=DONT_SCHOOL_LIST)
        # 从限制后的区域里推荐志愿
        showSchoolList_ByAveragePre_withArea = SelectRightSchool(schoolList=rightAreaSchool_ByAverage)

    return render_template("showMatchSchool.html", studentInfo=studentInfo, School_ALL=schoolListAll,
                           ByAverage_All=schoolDictByAverage, ByAverage_withArea=rightAreaSchool_ByAverage,
                           ByAverage_Select=showSchoolList_ByAveragePre,
                           ByAverage_Select_withArea=showSchoolList_ByAveragePre_withArea)


@app.route("/searchSchoolInfo", methods=["GET", "POST"])
def searchSchoolInfo():
    recv = request.json
    schoolCode = recv['SchoolCode']  # 接收并解析前端传来的学校代码
    conn = pool.connection()
    cursor = conn.cursor()
    # 开始计算学校
    sql = (
        f"select SchoolInfo.SchoolName,AcceptedInfo.AcceptedYear,AcceptedInfo.AcceptedScore,AcceptedInfo.AcceptedLine,AcceptedInfo.DirectToUnify "
        f"from AcceptedInfo, SchoolInfo where AcceptedInfo.SchoolCode = SchoolInfo.SchoolCode and SchoolInfo.SchoolCode = '{schoolCode}'")
    responseJson = {}
    if schoolCode:
        cursor.execute(sql)
        results = cursor.fetchall()
        cursor.close()
        conn.close()
        if len(results) > 0:
            responseJson['data'] = results
            responseJson['status'] = 1
            return jsonify(responseJson)
        else:
            responseJson['status'] = 0
            return jsonify(responseJson)
    else:
        responseJson['status'] = 0
        return jsonify(responseJson)


# 更新学校提供的预估分
@app.route("/UpdatePreScoreBySchool", methods=['POST'])
def UpdatePreScoreBySchool():
    recv = request.json
    schoolCode = recv['SchoolCode']  # 接收并解析前端传来的学校代码
    newValue = recv['newValue']

    responseJson = {}
    conn = pool.connection()
    cursor = conn.cursor()

    sql = f"update PreAcceptInfo set PreAcceptScoreBySchool='{newValue}' where SchoolCode = '{schoolCode}' "
    if schoolCode and newValue != 'None':
        cursor.execute(sql)
        conn.commit()
        cursor.close()
        conn.close()
        responseJson['status'] = 1
    else:
        responseJson['status'] = 0
    return jsonify(responseJson)


# 更新学校名称
@app.route("/UpdateSchoolName", methods=['POST'])
def UpdateSchoolName():
    recv = request.json
    schoolCode = recv['SchoolCode']  # 接收并解析前端传来的学校代码
    newValue = recv['newValue']
    # print(schoolCode, newValue)
    responseJson = {}
    conn = pool.connection()
    cursor = conn.cursor()

    sql = f"update SchoolInfo set SchoolName='{newValue}' where SchoolCode = '{schoolCode}' "
    if schoolCode:
        cursor.execute(sql)
        conn.commit()
        cursor.close()
        conn.close()
        responseJson['status'] = 1
    else:
        responseJson['status'] = 0
    return jsonify(responseJson)


# 更新算法预估分
@app.route("/UpdatePreScoreByAverage", methods=['POST'])
def UpdatePreScoreByAverage():
    recv = request.json
    schoolCode = recv['SchoolCode']  # 接收并解析前端传来的学校代码
    newValue = recv['newValue']
    # print(schoolCode, newValue)
    responseJson = {}
    conn = pool.connection()
    cursor = conn.cursor()

    sql = f"update PreAcceptInfo set PreAcceptScoreByAverage='{newValue}' where SchoolCode = '{schoolCode}' "
    if schoolCode:
        cursor.execute(sql)
        conn.commit()
        cursor.close()
        conn.close()
        responseJson['status'] = 1
    else:
        responseJson['status'] = 0
    return jsonify(responseJson)


@app.route("/UpdateStudentInfo", methods=['POST'])
def UpdateStudentInfo():
    recv = request.json
    fieldidx = recv['field']
    studentID = recv['studentID']
    newValue = recv['newValue']
    responseJson = {}
    conn = pool.connection()
    cursor = conn.cursor()
    fields = ["StudentID", "StudentName", "StudentScore", "SourceSchool", "StudentPhone", "RegisterTeacher", "AddTime",
              "StudentConfirm"]
    if fieldidx and studentID:
        sql = f"update StudentInfo set {fields[fieldidx]}='{newValue}' where StudentID = '{studentID}' "
        # print(sql)
        cursor.execute(sql)
        conn.commit()
        cursor.close()
        conn.close()
        responseJson['status'] = 1
    else:
        responseJson['status'] = 0
    return jsonify(responseJson)


@app.route("/showStudent")
def showStudent():
    return render_template('showStudent.html')


@app.route("//QueryTheStudentList", methods=['POST'])
def QueryTheStudentList():
    recv = request.json
    studentName = recv['studentName']
    teacherName = recv['teacherName']
    responseJson = {}
    conn = pool.connection()
    cursor = conn.cursor()
    # print(studentName)

    sql = f"select * from StudentInfo where StudentName like'%{studentName}%' or RegisterTeacher like '%{teacherName}%' order by StudentCOnfirm DESC,StudentScore DESC,AddTime DESC"
    if studentName or teacherName:
        cursor.execute(sql)
        data = cursor.fetchall()
        responseJson['status'] = 1
        responseJson['data'] = data
        cursor.close()
        conn.close()
    else:
        responseJson['status'] = 0
    return jsonify(responseJson)


if __name__ == '__main__':
    app.run(debug=True, host='127.0.0.1', port='5000')
